﻿using System;
using System.Text;
using System.Data;
using CRMFramework;
using System.Text.RegularExpressions;
using System.Collections.Generic;

namespace CRMBiz.Group
{
    public static class GroupHandler
    {
        public static DataTable GetGroupList(int pageIndex, int pageSizez, string Company, int GroupID)
        {
             
            DataTable dt = null;
            try
            {
                var sbSql = new StringBuilder("SELECT * from crm_group Where")
                .AppendFormat(" Company Like'%{0}%' AND (ParentGroupID={1} OR GroupID={1}) Order by GroupID", Company, GroupID);
                dt = DBhelper.ExecuteDataset(sbSql.ToString()).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public static DataSet GetGroupDetail(int GroupID)
        {
            DataSet ds = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@GroupID", GroupID),
                };

                string sbSql = "SELECT GroupID,GroupName,trim(GroupType)as GroupType,Description,Company from crm_group where GroupID=@GroupID;";
                ds = DBhelper.ExecuteDataset(sbSql, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static bool AddGroup(int ParentGroupID, string GroupName, string GroupType, string Description, string Company)
        {
            bool flag = false;
            try
            {
                var para1 = new[] {
                    DBhelper.SetParameter("@ParentGroupID", ParentGroupID),
                    DBhelper.SetParameter("@GroupName", GroupName),
                    DBhelper.SetParameter("@Company", Company),
                };

                string sbSql = "select GroupID,GroupName,Company from crm_group where GroupID=@ParentGroupID and GroupName=@GroupName and Company=@Company";
                DataTable dtdata = DBhelper.ExecuteDataset(sbSql, para1).Tables[0];
                if (dtdata.Rows.Count > 0)
                {
                    int ParentGroupID1 = Int32.Parse(dtdata.Rows[0]["GroupID"].ToString());
                    string GroupName1 = dtdata.Rows[0]["GroupName"].ToString();

                    if (ParentGroupID1 != ParentGroupID && GroupName1 != GroupName)
                    {
                        var para = new[] {
                            DBhelper.SetParameter("@ParentGroupID", ParentGroupID),
                            DBhelper.SetParameter("@GroupName", GroupName),
                            DBhelper.SetParameter("@GroupType", GroupType),
                            DBhelper.SetParameter("@Description", Description),
                            DBhelper.SetParameter("@Company", Company),
                        };
                        sbSql = string.Empty;
                        sbSql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description,Company) values(@ParentGroupID,@GroupName,@GroupType,@Description,@Company)";
                        DBhelper.ExecuteNonQuery(sbSql, para);
                        flag = true;
                    }
                }
                else
                {
                    if (ParentGroupID != 0)
                    {
                        var para = new[] {
                        DBhelper.SetParameter("@ParentGroupID", ParentGroupID),
                        DBhelper.SetParameter("@GroupName", GroupName),
                        DBhelper.SetParameter("@GroupType", GroupType),
                        DBhelper.SetParameter("@Description", Description),
                        DBhelper.SetParameter("@Company", Company),
                        };
                        //Check company for parent group:

                        DataTable dt = DBhelper.ExecuteDataset("Select Company from crm_group where GroupID=@ParentGroupID", para).Tables[0];
                        if (dt.Rows[0]["Company"] == DBNull.Value)
                        {
                            DBhelper.ExecuteNonQuery("update crm_group set Company=@Company where GroupID=@ParentGroupID", para);
                        }
                        else
                        {

                            string f_Company = "";
                            StringBuilder data = new StringBuilder();
                            string stringToCheck = dt.Rows[0]["Company"].ToString();
                            string[] stringArray = Company.Split(',');
                            foreach (string x in stringArray)
                            {
                                if (stringToCheck.Contains(x))
                                {
                                }
                                else
                                {
                                    data.Append(x).Append(",");
                                }

                            }
                            string str = "";
                            string result = data.ToString();
                            int i = result.LastIndexOf(",");
                            if (i > 0)
                            {
                                str = result.Remove(result.Length - 1, 1) + "";
                            }
                            else
                            {
                                str = result;
                            }
                            if (str == "")
                            {
                                f_Company = Company;
                            }
                            else
                            {
                                f_Company = dt.Rows[0]["Company"].ToString() + "," + str;
                            }
                            var para3 = new[] {
                                DBhelper.SetParameter("@ParentGroupID", ParentGroupID),
                                DBhelper.SetParameter("@f_Company", f_Company),
                                };


                            DBhelper.ExecuteNonQuery("Update crm_group set Company=@f_Company where GroupID=@ParentGroupID", para3);

                        }
                        sbSql = string.Empty;
                        sbSql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description,Company) values(@ParentGroupID,@GroupName,@GroupType,@Description,@Company)";
                        DBhelper.ExecuteNonQuery(sbSql, para);
                        flag = true;
                    }
                    else
                    {

                        dtdata = DBhelper.ExecuteDataset("SELECT GroupID FROM crm_group where groupname='Root'").Tables[0];
                        var para = new[] {
                        DBhelper.SetParameter("@ParentGroupID", dtdata.Rows[0]["GroupID"]),
                        DBhelper.SetParameter("@GroupName", GroupName),
                        DBhelper.SetParameter("@GroupType", GroupType),
                        DBhelper.SetParameter("@Description", Description),
                        DBhelper.SetParameter("@Company", Company)
                        };
                        sbSql = string.Empty;
                        sbSql = "insert into crm_group(ParentGroupID,GroupName,GroupType,Description,Company) values(@ParentGroupID,@GroupName,@GroupType,@Description,@Company)";
                        DBhelper.ExecuteNonQuery(sbSql, para);
                        flag = true;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool EditGroup(int GroupID, int ParentGroupID, string GroupName, string GroupType, string Description, string Company)
        {
            bool flag = false;
            string chk_qur = string.Empty;
            string sbSql = string.Empty;
            try
            {
                var para1 = new[] {
                    DBhelper.SetParameter("@GroupID", GroupID)
                };

                //DataTable dtdata = DBhelper.ExecuteDataset("Select GroupName from crm_group where GroupID=@GroupID", para1).Tables[0];
                //if (dtdata.Rows[0]["GroupName"].ToString() == "Process" || dtdata.Rows[0]["GroupName"].ToString() == "Root")
                //{
                //    flag = false;
                //}
                //else
                //{
                var para = new[] {
                    DBhelper.SetParameter("@GroupID", GroupID),
                    DBhelper.SetParameter("@ParentGroupID", ParentGroupID),
                    DBhelper.SetParameter("@GroupName", GroupName),
                    DBhelper.SetParameter("@GroupType", GroupType),
                    DBhelper.SetParameter("@Description", Description),
                    DBhelper.SetParameter("@Company", Company)
                    };

                DBhelper.ExecuteNonQuery("update crm_group set ParentGroupID=@ParentGroupID,GroupName=@GroupName,GroupType=@GroupType,Description=@Description,Company=@Company where GroupID=@GroupID", para);

                //Check company for parent group:

                DataTable dt = DBhelper.ExecuteDataset("select Company from crm_group where GroupID=@ParentGroupID", para).Tables[0];
                sbSql = string.Empty;
                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["Company"] == DBNull.Value)
                        DBhelper.ExecuteNonQuery("Update crm_group set Company=@Company where GroupID=@ParentGroupID", para);
                    else
                    {
                        string f_Company = "";
                        StringBuilder data = new StringBuilder();
                        string stringToCheck = dt.Rows[0]["Company"].ToString();
                        string[] stringArray = Company.Split(',');
                        foreach (string x in stringArray)
                        {
                            if (stringToCheck.Contains(x))
                            {
                            }
                            else
                            {
                                data.Append(x).Append(",");
                            }

                        }
                        string str = "";

                        string result = data.ToString();
                        int i = result.LastIndexOf(",");
                        if (i > 0)
                        {
                            str = result.Remove(result.Length - 1, 1) + "";
                        }
                        else
                        {
                            str = result;
                        }
                        if (str == "")
                        {
                            f_Company = Company;
                        }
                        else
                        {
                            f_Company = dt.Rows[0]["Company"].ToString() + "," + str;
                        }

                        var para3 = new[] {
                                DBhelper.SetParameter("@ParentGroupID", ParentGroupID),
                                DBhelper.SetParameter("@f_Company", f_Company),
                        };

                        DBhelper.ExecuteNonQuery("Update crm_group set Company=@f_Company where GroupID=@ParentGroupID", para3);
                    }
                }

                flag = true;
            }

            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static int CheckParentGroup(int GroupID, string GroupName, int ParentGroupID)
        {
            int retvalu = 0;
            try
            {
                if (GroupName == "Process" || GroupName == "Priority Queue" || GroupName == "Root")
                    retvalu = 1;
                else
                {
                    DataTable dt = DBhelper.ExecuteDataset("SELECT GroupID,ParentGroupID,(SELECT count(*) from crm_group) as Total FROM crm_group").Tables[0];
                    if (dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < int.Parse(dt.Rows[0]["Total"].ToString()); i++)
                        {
                            if (int.Parse(dt.Rows[i]["ParentGroupID"].ToString()) == GroupID)
                                retvalu = 1;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return retvalu;
        }

        public static bool DeleteGroup(int GroupID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@GroupID", GroupID),
                };
                DBhelper.ExecuteNonQuery("Delete from crm_group where GroupID=@GroupID", para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        private static string formateConditionText(string condition)
        {
            if (condition.Contains("("))
                return condition;
            int ORcount = new Regex(" or ").Matches(condition).Count;
            int Andcount = new Regex(" and ").Matches(condition).Count;
            if (ORcount >= 1 && Andcount == 0)
                condition = "( " + condition + " )";
            else if (ORcount >= 1 && Andcount >= 1)
            {
                string[] strTemp = { };

                if (Andcount >= 1)
                    strTemp = condition.Split(new string[] { " and " }, StringSplitOptions.None);
                condition = "";
                for (int i = 0; i < strTemp.Length; i++)
                {
                    if (new Regex(" or ").Matches(strTemp[i]).Count > 0)
                        strTemp[i] = formateConditionText(strTemp[i]);
                    else
                        strTemp[i] = strTemp[i];

                    if (i < strTemp.Length - 1)
                        condition += strTemp[i] + " or  ";
                    else
                        condition += strTemp[i];
                }

            }
            return condition;
        }
        // get group data by special definition like campaign manager.
        public static DataSet GetGroupDataByCustomDefinition(string description, int pageIndex, int pageSize)
        {
            try
            {
                DataSet ds = null;
                var strSql = new StringBuilder(description);
                strSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(strSql.ToString());
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataSet GetGroupDataByCustomDefinition(string description, int pageIndex, int pageSize,out int totalAmount)
        {
            try
            {
                DataSet ds = null;
                string commandWithTotal = description;
                int firstSelectIndex= description.ToUpper().IndexOf("SELECT");
                if (firstSelectIndex >-1)
                {
                    commandWithTotal = "SELECT SQL_CALC_FOUND_ROWS " + description.Substring(firstSelectIndex + 6);
                }
                var strSql = new StringBuilder(commandWithTotal);
                strSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDatasetWithTotalAmount(strSql.ToString(), out totalAmount);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //Get total amount 
        public static Int32 GetTotalCount(string description)
        {
            Int32 totalCount = 0;
            try
            {
                totalCount = DBhelper.CountAfterExecuteDataset(description);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return totalCount;
        }

        public static DataSet GetGroupDataFromQuery(string Type, string Description, string GroupName, string Days, int pageIndex, int pageSize, string sortingText)
        {
            DataSet ds = null;
            try
            {
                Description = Description.ToLower();
                string[] strSeparator = new string[] { "where", "order by" };
                string[] strCondition = Description.Split(strSeparator, StringSplitOptions.None);

                string strdays = string.Empty;

                if (Days == "30")
                    strdays = " DATEDIFF(CURDATE(),DATE(EditDate)) <= 30 AND";
                else if (Days == "90")
                    strdays = "  DATEDIFF(CURDATE(),DATE(EditDate)) <= 90 AND";
                else
                    strdays = string.Empty;

                if (Type == "Direct Application" || Type == "Direct Client")
                {
                    string strColumns = string.Empty;

                    string strQuery = string.Empty;
                    var index = Description.IndexOf(" from ");
                    if (index > -1)
                    {
                        strQuery = Description.Substring(index);
                    }

                    var strSql = new StringBuilder("Select ");
                    if (Type == "Direct Client")
                    {
                        strSql.Append(" ContactID as clientprofileid,ContactID,DateBirth,State,CreatedDate as client_CreateDate, EditDate as client_EditDate, concat(FirstName,' ',MiddleName,' ',SurName) as ClientName ,(Select count(Distinct app_id) ");
                        if (strCondition.Length == 2)
                        {
                            // If No Order by clause
                            strSql.Append(strQuery);

                            if (!string.IsNullOrEmpty(strdays))
                                strSql.Append(" AND " + strdays.Replace("AND", ""));

                            strSql.Append(" )as Total ").Append(strQuery);

                            if (!string.IsNullOrEmpty(strdays))
                                strSql.Append(" AND " + strdays.Replace("AND", ""));


                            if (!string.IsNullOrEmpty(sortingText))
                                strSql.Append(" ORDER by " + sortingText);
                        }
                        else if (strCondition.Length == 3)
                        {
                            // With Order By clasue
                            strSql.Append(" from crm_contact Where ").Append(formateConditionText(strCondition[1]));

                            if (!string.IsNullOrEmpty(strdays))
                                strSql.Append(" AND " + strdays.Replace("AND", ""));

                            strSql.Append(" )as Total from crm_application Where").Append(formateConditionText(strCondition[1]));

                            if (!string.IsNullOrEmpty(strdays))
                                strSql.Append(" AND " + strdays.Replace("AND", ""));

                            strSql.AppendFormat(" ORDER By {0}", strCondition[2]);
                        }
                    }
                    else if (Type == "Direct Application")
                    {
                        strSql.Append(" *,udf_SelectFirstApplicant(app_id) as applicant1,udf_SelectApplicantName(app_id) AS SecondApplicant,(Select count(Distinct app_id) ");
                        if (strCondition.Length == 2)
                        {
                            // If No Order by clause
                            strSql.Append(strQuery);

                            if (!string.IsNullOrEmpty(strdays))
                                strSql.Append(" AND " + strdays.Replace("AND", ""));

                            strSql.Append(" )as Total ").Append(strQuery);

                            if (!string.IsNullOrEmpty(strdays))
                                strSql.Append(" AND " + strdays.Replace("AND", ""));


                            if (!string.IsNullOrEmpty(sortingText))
                                strSql.Append(" ORDER by " + sortingText);
                        }
                        else if (strCondition.Length == 3)
                        {
                            // With Order By clasue
                            strSql.Append(" from crm_application Where ").Append(formateConditionText(strCondition[1]));

                            if (!string.IsNullOrEmpty(strdays))
                                strSql.Append(" AND " + strdays.Replace("AND", ""));

                            strSql.Append(" )as Total from crm_application Where").Append(formateConditionText(strCondition[1]));

                            if (!string.IsNullOrEmpty(strdays))
                                strSql.Append(" AND " + strdays.Replace("AND", ""));

                            strSql.AppendFormat(" ORDER By {0}", strCondition[2]);
                        }
                    }
                    strSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(strSql.ToString());
                    return ds;
                }
                else if (Type == "Application")
                    Type = "app_id";
                else if (Type == "Client")
                    Type = "ContactID";
                var strCommonQuery = new StringBuilder(" from view_groupdata where ");
                if (strCondition.Length == 2)
                {
                    if (Type == "app_id")
                    {
                        if (!string.IsNullOrEmpty(sortingText))
                            sortingText = " ORDER By " + sortingText;
                        else
                            sortingText = " ORDER By EditDate Desc";
                    }
                    else if (Type == "ContactID")
                    {
                        if (!string.IsNullOrEmpty(sortingText))
                            sortingText = " ORDER By " + sortingText;
                        else
                            sortingText = " ORDER By client_EditDate Desc";
                    }

                    int cnt = new Regex(" or ").Matches(strCondition[1]).Count;
                    if (cnt >= 1)
                        strCondition[1] = formateConditionText(strCondition[1]);
                    var sbSql = new StringBuilder("SELECT ");


                    if (!string.IsNullOrEmpty(strdays))
                        strCommonQuery.Append(strdays);

                    strCommonQuery.Append(strCondition[1]);


                    if (Type == "app_id")

                        sbSql.Append(" app_id,CAST(OfferedAmount as Decimal(10,0)) as OfferedAmount ,app_id as applicationprofileid,udf_SelectFirstApplicant(app_id) as applicant1,udf_SelectApplicantName(app_id) AS SecondApplicant, ")
                        .Append(" CAST(DesiredAmount as Decimal(10,0)) as DesiredAmount ,app_result,CreatedDate,EditDate,PurposeSummary,ReferrerVal,amoutProperties, (select count(DISTINCT app_id)  ");

                    else if (Type == "ContactID")
                        sbSql.Append("ContactID,concat(FirstName,' ',MiddleName,' ',SurName) as ClientName,DateBirth,State,client_EditDate,client_CreateDate,ContactID as clientprofileid, (Select count(DISTINCT ContactID) ");

                    sbSql.Append(strCommonQuery.ToString()).Append(" )as Total ");
                    sbSql.Append(strCommonQuery.ToString());
                    sbSql.Append(" group by ").Append(Type);
                    sbSql.Append(sortingText);

                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());

                }
                else if (strCondition.Length == 3)
                {
                    int cnt = new Regex(" or ").Matches(strCondition[1]).Count;
                    if (cnt >= 1)
                        strCondition[1] = formateConditionText(strCondition[1]);
                    var sbSql = new StringBuilder("SELECT ");

                    if (!string.IsNullOrEmpty(strdays))
                        strCommonQuery.Append(strdays);

                    strCommonQuery.Append(strCondition[1]);

                    if (Type == "app_id")
                        sbSql.Append(" app_id,CAST(OfferedAmount as Decimal(10,0)) as OfferedAmount ,app_id as applicationprofileid,udf_SelectFirstApplicant(app_id) as applicant1,udf_SelectApplicantName(app_id) AS SecondApplicant, " +
                        " CAST(DesiredAmount as Decimal(10,0)) as DesiredAmount ,app_result,CreatedDate,EditDate,PurposeSummary,ReferrerVal,amoutProperties, (select count(DISTINCT app_id) ");
                    else if (Type == "ContactID")
                        sbSql.Append("ContactID,concat(FirstName,' ',MiddleName,' ',SurName) as ClientName,DateBirth,State,client_EditDate,client_CreateDate,ContactID as clientprofileid, (Select count(DISTINCT ContactID) ");


                    sbSql.Append(strCommonQuery.ToString()).Append(" )as Total ");
                    sbSql.Append(strCommonQuery.ToString());
                    sbSql.Append(" group by ").Append(Type);

                    if (string.IsNullOrEmpty(sortingText))
                        sbSql.AppendFormat(" ORDER By {0}", strCondition[2]);
                    else
                        sbSql.Append(" ORDER By " + sortingText);

                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataTable GetParentGroupList(string company)
        {
            DataTable dt = null;
            try
            {
                dt = DBhelper.ExecuteDataset("SELECT GroupID,GroupName FROM crm_group Where Company like '%" + company + "%'").Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataTable GetFieldType(string fieldName)
        {
            DataTable dt = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@field_name", fieldName),
                };
                dt = DBhelper.ExecuteDataset("SELECT DATA_TYPE FROM view_getcolumnname where field_name=@field_name", para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public static DataTable GetResultIDValue()
        {
            DataTable dt = null;
            try
            {
                dt = DBhelper.ExecuteDataset("SELECT * FROM crm_lastresult").Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public static DataTable GetTreeGroupList(string Company)
        {

            DataTable dt = null;
            try
            {
                dt = DBhelper.ExecuteDataset(string.Format("SELECT * FROM crm_group where  Company LIKE '%{0}%'", Company)).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
    }
}
